﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using WebMinhHang.Libs;
using WebMinhHang.Lists;

namespace WebMinhHang
{
    public partial class FrmThongKeHangBanChay : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            try
            {
                if (!Page.IsPostBack)
                {
                    string dbName = lstDsDatabase.SelectedValue;
                    Connection.ConnectString(dbName);

                    cmbKyBaoCao.SelectedIndex = 17;
                    dateNgayBatDau.Enabled = true;
                    dateNgayKetThuc.Enabled = true;
                    DateTime now = DateTime.Now;
                    dateNgayBatDau.Date = new DateTime(now.Year, now.Month, now.Day, 0, 0, 0);
                    dateNgayKetThuc.Date = new DateTime(now.Year, now.Month, now.Day, 23, 59, 59);

                    foreach (NhomVatTuHangHoa nhomHang in DanhMucNhomVatTuHangHoa.Instance)
                    {
                        cmbNhomHang.Items.Add(nhomHang.Ten);
                    }
                }
            }
            catch (Exception ex)
            {
                string display = ex.Message;
                ClientScript.RegisterStartupScript(this.GetType(), "Thông báo", "alert('" + display + "');", true);
            }

        }

        protected void cmbKyBaoCao_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                ThoiDiemBaoCao.ThietLapKhoangThoiGianBaoCao(cmbKyBaoCao.Text, cmbKyBaoCao, dateNgayBatDau, dateNgayKetThuc);
            }
            catch (Exception ex)
            {
                string display = ex.Message;
                ClientScript.RegisterStartupScript(this.GetType(), "Thông báo", "alert('" + display + "');", true);
            }
        }

        protected void btnThongKe_Click(object sender, EventArgs e)
        {
            DsThongKeBanLe ds = new DsThongKeBanLe();

            //Lọc hàng theo nhóm
            if (cmbNhomHang.SelectedIndex != -1)
            {
                //Dieu kien loc la ID = cmbNhomHang.EditValue + ID cac nhom con.
                NhomVatTuHangHoa editValue = DanhMucNhomVatTuHangHoa.Instance.GetByTen(cmbNhomHang.SelectedItem.ToString().Trim());
                string filterNhomCon = "CONVERT('{" + editValue.ID.ToString() + "}','System.Guid'), ";
                DanhMucNhomVatTuHangHoa cacNhomCon = DanhMucNhomVatTuHangHoa.Instance.GetNhomVatTuHangHoaCon(editValue.ID);
                foreach (NhomVatTuHangHoa nhomCon in cacNhomCon)
                {
                    filterNhomCon += "CONVERT('{" + nhomCon.ID + "}','System.Guid'), ";
                    //filterNhomCon += string.Format(", '{0}'", nhomCon.ID);
                }
                if (filterNhomCon.EndsWith(", ")) filterNhomCon = filterNhomCon.Remove(filterNhomCon.Length - 2);

                ds.VatTuHangHoa.Clear();
                string filter = string.Format("NhomVatTuHangHoaID IN ({0}) AND TrangThai = 1 ", filterNhomCon);
                DataView dt = DanhMucVatTuHangHoa.Filter(filter).DataView;
                ds.VatTuHangHoa.Merge(dt.ToTable());
            }

            //Get database name form listbox (value)
            string dbName = lstDsDatabase.SelectedValue;

            string connectionString = Connection.ConnectString(dbName);
            SqlConnection con = new SqlConnection(connectionString);

            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand(); cmd.CommandTimeout = 3 * 60; //3 Phút

                string nhaCungCapCondition = "1 = 1";

                string vatTuHangHoaCondition = "1 = 1";
                if (cmbNhomHang.SelectedIndex != -1)
                {
                    vatTuHangHoaCondition = "C.VatTuHangHoa1ID IN  (";
                    foreach (DataRow row in ds.VatTuHangHoa.Rows)
                    {
                        vatTuHangHoaCondition += string.Format("'{0}', ", row["ID"]);
                    }
                    if (vatTuHangHoaCondition.EndsWith("', ")) vatTuHangHoaCondition = vatTuHangHoaCondition.Substring(0, vatTuHangHoaCondition.Length - 2) + ")";
                    else vatTuHangHoaCondition = "1 = 0";
                }

                string loaiChungTuBanCondition = "''";
                loaiChungTuBanCondition = "N'HoaDonBanHang', N'HoaDonBanLe'";

                cmd.CommandText = string.Format("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");
                cmd.ExecuteNonQuery();

                cmd.CommandText = string.Format("SELECT D.Ten TenHangHoa, E.Ten TenNhomHangHoa, D.DonViTinh, DTNV.Ten TenDoiTac, SUM(C.Value1) SoLuong, C.Value12 GiaBia, \n" +
                                              "   SUM(C.Value13) ThanhTienGiaBia, AVG(C.Value8) ChietKhau, AVG(C.Value6) GiaThanhToan, SUM(C.Value7) ThanhTienThanhToan \n" +
                                              "FROM LoaiChungTu LCT INNER JOIN ChungTu A ON LCT.ID = A.LoaiChungTuID  \n" +
                                              "	INNER JOIN ChungTu B ON A.ChungTuID = B.ChungTuGocID  \n" +
                                              "	INNER JOIN  KhoanMucChungTu C ON B.ChungTuID = C.ChungTuID  \n" +
                                              "	INNER JOIN VatTuHangHoa D ON C.VatTuHangHoa1ID = D.ID  \n" +
                                              "	LEFT JOIN NhomVatTuHangHoa E ON D.NhomVatTuHangHoaID = E.ID  \n" +
                                              "	LEFT JOIN DoiTuongNghiepVu DTNV ON D.DoiTuongNghiepVuID = DTNV.ID \n" +
                                              "	INNER JOIN Kho ON B.Kho1ID = Kho.ID  \n" +
                                              "WHERE LCT.Ma IN({2}) AND A.GhiSo = 1  \n" +
                                              "	AND @ThoiDiemDau <= A.Ngay AND A.Ngay <= @ThoiDiemCuoi AND A.GhiSo = 1\n" +
                                              "	AND	{0} \n" +
                                              "	AND {1} \n" +
                                              "    AND C.Value5 IS NOT NULL AND C.Value3 IS NOT NULL \n" +
                                              "    GROUP BY D.Ten, E.Ten, D.DonViTinh, C.Value12, Kho.Ma, DTNV.Ten \n" +
                                              "    ORDER BY SUM(C.Value1) DESC \n",
                                              nhaCungCapCondition, vatTuHangHoaCondition, loaiChungTuBanCondition);

                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@ThoiDiemDau", dateNgayBatDau.Date);
                cmd.Parameters.AddWithValue("@ThoiDiemCuoi", dateNgayKetThuc.Date);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                ds.Detail_ThongKeBanHang.Clear();
                da.Fill(ds.Detail_ThongKeBanHang);
                gridResult.DataSource = ds.Detail_ThongKeBanHang;
                gridResult.DataBind();
            }
            catch (Exception ex)
            {
                string display = ex.Message;
                ClientScript.RegisterStartupScript(this.GetType(), "Thông báo", "alert('" + display + "');", true);
            }
            finally
            {
                con.Close();
                con = null;
            }
        }

        protected void lstDsDatabase_SelectedIndexChanged(object sender, EventArgs e)
        {
            string dbName = lstDsDatabase.SelectedValue;
            Connection.ConnectString(dbName);

            gridResult.DataSource = null;
            gridResult.DataBind();
        }
    }
}
